CREATE OR REPLACE FUNCTION sp_agenda_insert(
	_id_project	integer,
	_id_event	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_author	integer DEFAULT null,
	_id_approver	integer DEFAULT null,
	_start_time	timestamp without time zone DEFAULT null,
	_id_venue	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO agenda(id_project,id_event,name,code,description,id_author,id_approver,start_time,id_venue,status,notification,creator,updator,deletion_time)
	VALUES(_id_project,_id_event,_name,_code,_description,_id_author,_id_approver,_start_time,_id_venue,_status,_notification,_creator,_creator,null);
RETURN currval('agenda_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_agenda_select(_id	integer)
RETURNS SETOF agenda
AS $$
BEGIN
	RETURN QUERY SELECT * FROM agenda WHERE id = _id;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_agenda_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_event	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_author	integer DEFAULT null,
	_id_approver	integer DEFAULT null,
	_start_timeFrom	timestamp without time zone DEFAULT null,
	_start_timeTo	timestamp without time zone DEFAULT null,
	_id_venue	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS SETOF agenda
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM agenda WHERE
	 	(_id is null OR id = _id)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_id_event is null OR id_event = _id_event)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_author is null OR id_author = _id_author)	
	AND (_id_approver is null OR id_approver = _id_approver)	
	AND (_start_timeFrom is null OR start_time >= _start_timeFrom)
	AND	(_start_timeTo is null OR start_time <= _start_timeTo)	
	AND (_id_venue is null OR id_venue = _id_venue)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_agenda_count (
	_id	integer DEFAULT null,
	_id_project	integer DEFAULT null,
	_id_event	integer DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_description	character varying DEFAULT null,
	_id_author	integer DEFAULT null,
	_id_approver	integer DEFAULT null,
	_start_timeFrom	timestamp without time zone DEFAULT null,
	_start_timeTo	timestamp without time zone DEFAULT null,
	_id_venue	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM agenda WHERE
	 	(_id is null OR id = _id)	
	AND (_id_project is null OR id_project = _id_project)	
	AND (_id_event is null OR id_event = _id_event)	
	AND (_name is null OR name = _name)	
	AND (_code is null OR code = _code)	
	AND (_description is null OR description = _description)	
	AND (_id_author is null OR id_author = _id_author)	
	AND (_id_approver is null OR id_approver = _id_approver)	
	AND (_start_timeFrom is null OR start_time >= _start_timeFrom)
	AND	(_start_timeTo is null OR start_time <= _start_timeTo)	
	AND (_id_venue is null OR id_venue = _id_venue)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_agenda_delete(_id integer)
	RETURNS INT as
$$
BEGIN
		DELETE FROM agenda
 		WHERE "id" = _id;
		RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_agenda_update 
(	_id integer,
	_id_project integer,
	_id_event integer,
	_name character varying(255),
	_code character varying(20),
	_description character varying,
	_id_author integer,
	_id_approver integer,
	_start_time timestamp without time zone,
	_id_venue integer,
	_status integer,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE agenda 
	SET 		id_project = _id_project,
		id_event = _id_event,
		name = _name,
		code = _code,
		description = _description,
		id_author = _id_author,
		id_approver = _id_approver,
		start_time = _start_time,
		id_venue = _id_venue,
		status = _status,
		notification = _notification,
		updated = _now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
